﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
namespace ERP.Classes
{
    public class cs_erp_hanghoaInfo
    {
        private int _id_hh;
        private string _mahh;
        private string _tenhh;
        private int _id_nhom;
        private string _quycach;
        private int _dvt;
        private bool _nguyenlieu;
        private bool _hienthi;
        private double _giaban;
        private double _thue;

        public cs_erp_hanghoaInfo()
        {
            this._id_hh = 0;
            this._mahh = " ";
            this._tenhh = " ";
            this._id_nhom = 0;
            this._quycach = " ";
            this._dvt = 0;
            this._nguyenlieu = false;
            this._hienthi = true;
            this._giaban = 0;
            this._thue = 0;

        }
        public int id_hh
        {
            get
            {
                return this._id_hh;
            }
            set
            {
                this._id_hh = value;
            }
        }
        public string mahh
        {
            get
            {
                return this._mahh;
            }
            set
            {
                this._mahh = value;
            }
        }
        public string tenhh
        {
            get
            {
                return this._tenhh;
            }
            set
            {
                this._tenhh = value;
            }
        }
        public int id_nhom
        {
            get
            {
                return this._id_nhom;
            }
            set
            {
                this._id_nhom = value;
            }
        }
        public string quycach
        {
            get
            {
                return this._quycach;
            }
            set
            {
                this._quycach = value;
            }
        }
        public int dvt
        {
            get
            {
                return this._dvt;
            }
            set
            {
                this._dvt = value;
            }
        }
        public bool nguyenlieu
        {
            get
            {
                return this._nguyenlieu;
            }
            set
            {
                this._nguyenlieu = value;
            }
        }
        public bool hienthi
        {
            get
            {
                return this._hienthi;
            }
            set
            {
                this._hienthi = value;
            }
        }
        public double giaban
        {
            get
            {
                return this._giaban;
            }
            set
            {
                this._giaban = value;
            }
        }
        public double thue
        {
            get
            {
                return this._thue;
            }
            set
            {
                this._thue = value;
            }
        }

        public int ExcuteStoreProcedure(string storeProcedure, int option)
        {
            string[] paraName = new string[11];
            paraName[0] = "@id_hh";
            paraName[1] = "@mahh";
            paraName[2] = "@tenhh";
            paraName[3] = "@id_nhom";
            paraName[4] = "@quycach";
            paraName[5] = "@dvt";
            paraName[6] = "@nguyenlieu";
            paraName[7] = "@hienthi";
            paraName[8] = "@giaban";
            paraName[9] = "@thue";
            paraName[10] = "@option";
            object[] paraValue = new object[11];
            paraValue[0] = this._id_hh;
            paraValue[1] = this._mahh;
            paraValue[2] = this._tenhh;
            paraValue[3] = this._id_nhom;
            paraValue[4] = this._quycach;
            paraValue[5] = this._dvt;
            paraValue[6] = this._nguyenlieu;
            paraValue[7] = this._hienthi;
            paraValue[8] = this._giaban;
            paraValue[9] = this._thue;
            paraValue[10] = option;
            csCSDL csdl = new csCSDL();
            int i = 0;
            i = csdl.CreateParameters(paraName, 11, paraValue);
            return csdl.ExcuteStoreProcedure(storeProcedure);
        }

        public int ExcuteProcedure(int option)
        {
            return this.ExcuteStoreProcedure("Proc_ERP_HANGHOA", option);
        }

        
        public int Get_Info()
        {
            csCSDL csdl = new csCSDL();
            DataSet ds = new DataSet("ds");
            try
            {
                ds = csdl.OpenTable("ERP_HANGHOA", "id_hh = " + this._id_hh);
                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    return 0;
                }
                this._id_hh = Convert.ToInt32(ds.Tables[0].Rows[0]["id_hh"].ToString().Trim());
                this._mahh = ds.Tables[0].Rows[0]["mahh"].ToString().Trim();
                this._tenhh = ds.Tables[0].Rows[0]["tenhh"].ToString().Trim();
                this._id_nhom = Convert.ToInt32(ds.Tables[0].Rows[0]["id_nhom"].ToString().Trim());
                this._quycach = ds.Tables[0].Rows[0]["quycach"].ToString().Trim();
                this._dvt = Convert.ToInt32(ds.Tables[0].Rows[0]["dvt"].ToString().Trim());
                this._nguyenlieu = Convert.ToBoolean(ds.Tables[0].Rows[0]["nguyenlieu"].ToString().Trim());
                this._hienthi = Convert.ToBoolean(ds.Tables[0].Rows[0]["hienthi"].ToString().Trim());
                this._giaban = Convert.ToDouble(ds.Tables[0].Rows[0]["giaban"].ToString().Trim());
                this._thue = Convert.ToDouble(ds.Tables[0].Rows[0]["thue"].ToString().Trim());

                return 1;
            }
            catch
            {
                return -1;
            }
        }
        public DataSet Get_List()
        {
            csCSDL csdl = new csCSDL();
            DataSet ds = new DataSet("ds");
            try
            {
                string sql = "select id_hh, mahh, tenhh, tennhomhh, quycach, donvitinh, nguyenlieu, hienthi, giaban, thue, 0 as soluong "
                + " from ERP_HANGHOA"
                + " left join ERP_DVT on (dvt = id_dvt)"
                + " left join ERP_NHOMHANG on (id_nhomhhh = id_nhom)";
                ds = csdl.SelecSQL(sql);

                return ds;
            }
            catch
            {
                return null;
            }
        }

        public DataSet Get_List_by_HD(int hd)
        {
            csCSDL csdl = new csCSDL();
            DataSet ds = new DataSet("ds");
            try
            {
                string sql = "select id_hh, mahh, tenhh, tennhomhh, quycach, donvitinh, nguyenlieu, hienthi, giaban, thue, soluong "
                + " from ERP_HANGHOA, ERP_DVT, ERP_NHOMHANG, ERP_CHITIET_HOADON"
                + " where nguyenlieu = 0 and dvt = id_dvt and id_nhomhhh = id_nhom and id_hh = id_monan and id_hoadon = " + hd
                + " union all"
                + " select id_hh, mahh, tenhh, tennhomhh, quycach, donvitinh, nguyenlieu, hienthi, giaban, thue, 0 as soluong "
                + " from ERP_HANGHOA, ERP_DVT, ERP_NHOMHANG "
                + " where nguyenlieu = 0 and dvt = id_dvt and id_nhomhhh = id_nhom "
                + " and id_hh not in (select id_monan from ERP_CHITIET_HOADON where id_hoadon = " + hd + ")";
                ds = csdl.SelecSQL(sql);

                return ds;
            }
            catch
            {
                return null;
            }
        }

        public DataSet Get_List_by_HD_TT(int hd)
        {
            csCSDL csdl = new csCSDL();
            DataSet ds = new DataSet("ds");
            try
            {
                string sql = "select id_hh, mahh, tenhh, tennhomhh, quycach, donvitinh, nguyenlieu, hienthi, giaban, thue, soluong "
                + " from ERP_HANGHOA, ERP_DVT, ERP_NHOMHANG, ERP_CHITIET_HOADON"
                + " where dvt = id_dvt and id_nhomhhh = id_nhom and id_hh = id_monan and id_hoadon = " + hd
                + " union all"
                + " select id_hh, mahh, tenhh, tennhomhh, quycach, donvitinh, nguyenlieu, hienthi, giaban, thue, 0 as soluong "
                + " from ERP_HANGHOA, ERP_DVT, ERP_NHOMHANG "
                + " where dvt = id_dvt and id_nhomhhh = id_nhom "
                + " and id_hh not in (select id_monan from ERP_CHITIET_HOADON where id_hoadon = " + hd + ")";
                ds = csdl.SelecSQL(sql);

                return ds;
            }
            catch
            {
                return null;
            }
        }

        public DataSet Get_List_Nhapkho(int nk)
        {
            csCSDL csdl = new csCSDL();
            DataSet ds = new DataSet("ds");
            try
            {
                string sql = "select a.id_hh, a.mahh, a.tenhh, c.tennhomhh, a.quycach, b.donvitinh, a.nguyenlieu, a.hienthi, d.dongia, d.soluong "
                + " from ERP_HANGHOA a, ERP_DVT b, ERP_NHOMHANG c, ERP_CHITIET_NHAPKHO d"
                + " where a.dvt = b.id_dvt and c.id_nhomhhh = a.id_nhom and a.id_hh = d.mahh and d.id_lannhap = " + nk
                + " union all"
                + " select a.id_hh, a.mahh, a.tenhh, c.tennhomhh, a.quycach, b.donvitinh, a.nguyenlieu, a.hienthi, 0 as dongia, 0 as soluong "
                + " from ERP_HANGHOA a, ERP_DVT b, ERP_NHOMHANG c"
                + " where a.dvt = b.id_dvt and c.id_nhomhhh = a.id_nhom "
                + " and a.id_hh not in (select mahh from ERP_CHITIET_NHAPKHO where id_lannhap = " + nk + ")";
                ds = csdl.SelecSQL(sql);

                return ds;
            }
            catch
            {
                return null;
            }
        }

        public DataSet Get_List_Hangdu()
        {
            csCSDL csdl = new csCSDL();
            DataSet ds = new DataSet("ds");
            try
            {
                string sql = "select cast (0 as bit) as chon, id_chitiet_nhapkho, e.noidung, f.tenkho, a.tenhh, c.tennhomhh, b.donvitinh, a.nguyenlieu, d.dongia, d.soluong "
                + " from ERP_HANGHOA a, ERP_DVT b, ERP_NHOMHANG c, ERP_CHITIET_NHAPKHO d, ERP_NHAPKHO e, ERP_KHOHANG f"
                + " where a.dvt = b.id_dvt and c.id_nhomhhh = a.id_nhom and a.id_hh = d.mahh "
                + " and d.id_lannhap = e.id_lannhap and e.makho = f.id_khohang and d.soluong*d.nhan > 0 and d.hangdu = 1";
                ds = csdl.SelecSQL(sql);

                return ds;
            }
            catch
            {
                return null;
            }
        }

        public DataSet Get_List_Xuatkho(int khoxuat)
        {
            csCSDL csdl = new csCSDL();
            DataSet ds = new DataSet("ds");
            try
            {
                string sql = "select a.id_hh, a.mahh, a.tenhh, c.tennhomhh, a.quycach, b.donvitinh, a.nguyenlieu, a.hienthi, d.dongia, d.soluong, 0 as soluongxuat "
                + " from ERP_HANGHOA a, ERP_DVT b, ERP_NHOMHANG c, ERP_CHITIET_NHAPKHO d, ERP_NHAPKHO e"
                + " where a.dvt = b.id_dvt and c.id_nhomhhh = a.id_nhom and a.id_hh = d.mahh"
                + " and e.manx = 1 and e.id_lannhap = d.id_lannhap and d.soluong > 0 and e.trangthai = 1"
                + " and e.makho = " + khoxuat;
                ds = csdl.SelecSQL(sql);

                return ds;
            }
            catch
            {
                return null;
            }
        }

        public DataSet Get_List_Giaohang(int hd)
        {
            csCSDL csdl = new csCSDL();
            DataSet ds = new DataSet("ds");
            try
            {
                string sql = "select id_hh, mahh, tenhh, tennhomhh, quycach, donvitinh, nguyenlieu, hienthi, giaban, thue, soluong "
                + " from ERP_HANGHOA, ERP_DVT, ERP_NHOMHANG, ERP_CHITIET_HOADON"
                + " where dvt = id_dvt and id_nhomhhh = id_nhom and id_hh = id_monan and id_hoadon = " + hd;
                
                ds = csdl.SelecSQL(sql);

                return ds;
            }
            catch
            {
                return null;
            }
        }

        public DataSet Get_List(int hd)
        {
            csCSDL csdl = new csCSDL();
            DataSet ds = new DataSet("ds");
            try
            {
                string sql = "select id_hh, mahh, tenhh, tennhomhh, quycach, donvitinh, nguyenlieu, hienthi, giaban, thue, soluong "
                + " from ERP_HANGHOA, ERP_DVT, ERP_NHOMHANG, ERP_CHITIET_HOADON"
                + " where nguyenlieu = 1 and dvt = id_dvt and id_nhomhhh = id_nhom and id_hh = id_monan and id_hoadon = " + hd
                + " union all"
                + " select id_hh, mahh, tenhh, tennhomhh, quycach, donvitinh, nguyenlieu, hienthi, giaban, thue, 0 as soluong "
                + " from ERP_HANGHOA, ERP_DVT, ERP_NHOMHANG "
                + " where nguyenlieu = 1 and dvt = id_dvt and id_nhomhhh = id_nhom "
                + " and id_hh not in (select id_monan from ERP_CHITIET_HOADON where id_hoadon = " + hd + ")";
                ds = csdl.SelecSQL(sql);

                return ds;
            }
            catch
            {
                return null;
            }
        }

        public DataSet Get_List_GV(int hd)
        {
            csCSDL csdl = new csCSDL();
            DataSet ds = new DataSet("ds");
            try
            {
                string sql = "select a.id_hh, a.mahh, a.tenhh, c.tennhomhh, a.quycach, b.donvitinh, d.soluong, d.conlai, a.giaban "
                + " from ERP_HANGHOA a, ERP_DVT b, ERP_NHOMHANG c, ERP_HOADON_NGUYENLIEU d"
                + " where a.dvt = b.id_dvt and c.id_nhomhhh = a.id_nhom and a.id_hh = d.id_nguyenlieu and d.id_hoadon = " + hd
                + " union all"
                + " select a.id_hh, a.mahh, a.tenhh, c.tennhomhh, a.quycach, b.donvitinh, 0 as soluong, 0 as conlai, a.giaban  "
                + " from ERP_HANGHOA a, ERP_DVT b, ERP_NHOMHANG c "
                + " where a.nguyenlieu = 1 and a.dvt = b.id_dvt and c.id_nhomhhh = a.id_nhom "
                + " and a.id_hh not in (select id_nguyenlieu from ERP_HOADON_NGUYENLIEU where id_hoadon = " + hd + ")";
                ds = csdl.SelecSQL(sql);

                return ds;
            }
            catch
            {
                return null;
            }
        }

        public DataSet Get_List_nguyenlieu(int hd)
        {
            csCSDL csdl = new csCSDL();
            DataSet ds = new DataSet("ds");
            try
            {
                string sql = "select a.id_hh, a.mahh, a.tenhh, c.tennhomhh, a.quycach, b.donvitinh, d.soluong, d.conlai, a.giaban "
                + " from ERP_HANGHOA a, ERP_DVT b, ERP_NHOMHANG c, ERP_HOADON_NGUYENLIEU d"
                + " where a.dvt = b.id_dvt and c.id_nhomhhh = a.id_nhom and a.id_hh = d.id_nguyenlieu and d.id_hoadon = " + hd;
                ds = csdl.SelecSQL(sql);

                return ds;
            }
            catch
            {
                return null;
            }
        }

        public DataSet Get_List_Hanghoa_tu_NhomHH()
        {
            csCSDL csdl = new csCSDL();
            DataSet ds = new DataSet("ds");
            try
            {
                string sql = "select id_hh, mahh, tenhh "
                + " from ERP_HANGHOA"
                + " where hienthi = 1 and id_nhom = " + this.id_nhom;
                ds = csdl.SelecSQL(sql);

                return ds;
            }
            catch
            {
                return null;
            }
        }
    }


}
